package com.sky.blue.util;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static Session session;

    public static void main( String[] args ) throws IOException {

        InputStream resourceAsStream = ClassLoader.getSystemClassLoader()
                .getResourceAsStream("config.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        String ip = properties.getProperty("ip");
        String db = properties.getProperty("db");
        String name = properties.getProperty("name");
        String password = properties.getProperty("password");
        String host_ip = properties.getProperty("host_ip");
        String host_name = properties.getProperty("host_name");
        String host_password = properties.getProperty("host_password");

        int localPort = 0;
        try {
            localPort = openSSH(host_ip,host_name,host_password);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://"+ip+":"+localPort+"/"+db+"?characterEncoding=utf8&useSSL=false",name,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "select version()";
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                String version = rs.getString("version()");
                System.out.println("mysql version: "+version);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        closeSSH();
    }

    public static int openSSH(String host,String username,String password) throws Exception{

        JSch jSch =new JSch();
        session = jSch.getSession(username,host,22);
        session.setPassword(password);
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();
        System.out.println(session.getServerVersion());
        int assinged_port = session.setPortForwardingL(3307,host,3306);
        System.out.println("localhost:"+assinged_port);
        return assinged_port;

    }

    public static void closeSSH(){
        session.disconnect();
    }
}
